Excel 2013 All-in-One For Dummies by Greg Harvey

Author:Greg Harvey
Language: eng
Format: epub, pdf
Publisher: John Wiley and Sons, Inc.
Published: 2013-02-05T16:00:00+00:00

Chapter 3: Date and Time Formulas

In This Chapter

Understanding dates and times in Excel

Creating formulas that calculate elapsed dates and times

Using the Date functions

Using the Time functions

Creating formulas that use dates and times can be a little confusing if you don’t have a good understanding of how Excel treats these types of values. After you’re equipped with this understanding, you can begin to make good use of the many Date and Time functions that the program offers.

This chapter begins with a quick overview of date and time numbers in Excel and how you can use them to build simple formulas that calculate differences between elapsed dates and times. The chapter goes on to survey Excel built-in Date and Time functions, including the Date functions that are available after you’ve installed and activated the Analysis ToolPak add-in.

Understanding Dates and Times

Excel doesn’t treat the dates and times that you enter in the cells of your worksheet as simple text entries. (For more information on inputting numbers in a spreadsheet, see Book II, Chapter 1.) Any entry with a format that resembles one of the date and time number formats utilized by Excel is automatically converted, behind the scenes, into a serial number. In the case of dates, this serial number represents the number of days that have elapsed since the beginning of the 20th century so that January 1, 1900, is serial number 1; January 2, 1900, is serial number 2; and so forth. In the case of times, this serial number is a fraction that represents the number of hours, minutes, and seconds that have elapsed since midnight, which is serial number 0.00000000, so that 12:00:00 p.m. (noon) is serial number 0.50000000; 11:00:00 p.m. is 0.95833333; and so forth.

As long as you format a numeric entry so that it conforms to a recognized date or time format, Excel enters it as a date or time serial number. Only when you enter a formatted date or time as a text entry (by prefacing it with an apostrophe) or import dates and times as text entries into a worksheet do you have to worry about converting them into date and time serial numbers, which enables you to build spreadsheet formulas that perform calculations on them.

Changing the Regional date settings

Excel isn’t set up to automatically recognize European date formats in which the number of the day precedes the number of the month and year. For example, you may want 6/11/2014 to represent November 6, 1969, rather than June 11, 2014. If you’re working with a spreadsheet that uses this type of European date system, you have to customize the Windows Regional settings for the United States so that the Short Date format in Windows programs, such as Excel and Word 2013, use the D/m/yyyy (day, month, year) format rather than the default M/d/yyyy (month, day, year) format.

You can do this by following these steps:

1. Open the Windows Control Panel in Category View.

To do this in Windows 8, from the Start screen, type con and then click the Control Panel item in the Search Results screen.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.